package dao;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;

import TouristManagement.CheckInNotValid;

public class SiteVisiteDAO implements Serializable{
	
	MySqlConnecte myConn = new  MySqlConnecte();
	public String nomSite;
	
	public SiteVisiteDAO(String nomSite) {
		super();
		this.nomSite = nomSite;
	}
	
	public void createVisite(int idSite, int idTouriste) throws CheckInNotValid
	{
		try
		{
			final String queryOrdre = "select ifnull(max(ORDRE)+1,1) from VISITE where IDSITE=? and IDTOURISTE=? and now() between (select DATEIN from TOURISTE where IDTOURISTE=?) and (select DATEOUT from TOURISTE where IDTOURISTE=?) and DATEIN is null";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			preparedQuery.setInt(2, idTouriste);
			preparedQuery.setInt(3, idTouriste);
			preparedQuery.setInt(4, idTouriste);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			final int ordre = res.getInt(1);
			if(ordre<=0)
			{
				throw new CheckInNotValid("");
			}
			
			final String query = "update VISITE set ORDRE = ?, DATEIN=now() where IDSITE=? and IDTOURISTE=?";
			preparedQuery = this.myConn.getConnection().prepareStatement(query);
			preparedQuery.setInt(1, ordre);
			preparedQuery.setInt(2, idSite);
			preparedQuery.setInt(3, idTouriste);
			preparedQuery.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace(System.err);
			throw new CheckInNotValid(e.getMessage());
		}		
	}
	
	public static void main(String ...strings) throws Exception
	{
		new SiteVisiteDAO("").createVisite(1, 1);
	}

	//Check un tourist a dï¿½jï¿½ visitï¿½ d'un site 
	public boolean estVisite(short numTourist){
		try{				
			String sqlCommand="SELECT COUNT(*) FROM "+nomSite+" WHERE numTourist="+numTourist+" and DateVisite is not null";
			ResultSet rs = myConn.getList(sqlCommand);
			short cpt=0;
			if (rs.next()){
				cpt=rs.getShort(1);
			}  
			return (cpt==0);
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	//Ajouter d'une tourist ï¿½ un site
	public boolean addVisite(short numTourist,Date dateDebut,Date dateFin){
		try{	
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String sqlCommand = "INSERT INTO "+nomSite+" (numTourist,horaireEntree,horaireSortie,dateDebutValide,dateFinValide,niveauSatisfaction) VALUES ("+numTourist+","+null+","+null+",'"+sdf.format(dateDebut)+"','"+sdf.format(dateFin)+"',"+null+")";
			return myConn.updateList(sqlCommand);
		} catch(Exception e) {
			System.out.println("Tourist no "+numTourist+ " can not be added");
			e.printStackTrace();
			return false;
		}
	}
	
//	//Ajouter d'une tourist ï¿½ tous l'ensemble des sites
//	public boolean addVisiteAllSite(short numTourist,Date dateDebut,Date dateFin,int nbSite){
//		try{	
//			SiteVisiteDAO svd =new SiteVisiteDAO();
//			boolean res=false;
//			for (int i=1;i<=nbSite;i++){
//				res = svd.addVisite(nomSite,dateDebut,dateFin,i);
//				if (res=false) return res;
//			}
//			return res;
//		} catch(Exception e) {
//			System.out.println("Tourist nï¿½ "+numTourist+ " can not be added");
//			e.printStackTrace();
//			return false;
//		}
//	}
	
	//Set DateVisite au moment de l'entree d'une tourist 
	public boolean setDateVisite(short numTourist,Date dateVisite  ){
		try{
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String sqlCommand = "UPDATE "+nomSite+" SET DateVisite = '"+sdf.format(dateVisite)+"' WHERE numTourist="+numTourist+"";
			return myConn.updateList(sqlCommand);
		} catch(Exception e) {
			System.out.println("Error: setDateViste of tourist "+numTourist+" to the site "+nomSite+"");
			e.printStackTrace();
			return false;
		}
	}
	
	//Set horaireEntree
	public boolean setHoraireEntree(short numTourist,Date horaireEntree  ){
		try{	
			SimpleDateFormat sdf=new SimpleDateFormat("HH:mm:ss");
			String sqlCommand = "UPDATE "+nomSite+" SET horaireEntree = '"+sdf.format(horaireEntree)+"' WHERE numTourist="+numTourist+"";
			return myConn.updateList(sqlCommand);
		} catch(Exception e) {
			System.out.println("Error: setHoraireEntree of tourist "+numTourist+ " to the site "+nomSite+"");
			e.printStackTrace();
			return false;
		}
	}
	
	//Set horaireSortie
	public boolean setHoraireSortie(short numTourist,Date horaireEntree  ){
		try{	
			SimpleDateFormat sdf=new SimpleDateFormat("HH:mm:ss");
			String sqlCommand = "UPDATE "+nomSite+" SET horaireSortie = '"+sdf.format(horaireEntree)+"' WHERE numTourist="+numTourist+"";
			return myConn.updateList(sqlCommand);
		} catch(Exception e) {
			System.out.println("Error: setHoraireSortie of tourist "+numTourist+ " to the site "+nomSite+"");
			e.printStackTrace();
			return false;
		}
	}
	
	//Set Avis
	public boolean setAvis(short numTourist,short niveauSatisfaction){
		try{	
			String sqlCommand = "UPDATE "+nomSite+" SET niveauSatisfaction = "+niveauSatisfaction+" WHERE numTourist="+numTourist+"";
			return myConn.updateList(sqlCommand);
		} catch(Exception e) {
			System.out.println("Error: setAvis of tourist "+numTourist+ " of the site "+nomSite+"");
			e.printStackTrace();
			return false;
		}
	}

	public float getMoyenneVisite(){
		try{
			String sqlCommand = "SELECT avg(minute(timediff(HoraireSortie,HoraireEntree))) FROM "+nomSite+" WHERE HoraireSortie is not null AND HoraireEntree is not null";
			ResultSet rs = myConn.getList(sqlCommand);
			float res=0;
			if (rs.next()){
				res=rs.getFloat(1);
			}
			return res;
		} catch(Exception e) {
			// Il y avait une erreur
			return -1;
		}
	}
	
	//Durï¿½e max visite en minutes 
	public short getMaxVisite(){
		try{
			String sqlCommand = "SELECT max(minute(timediff(HoraireSortie,HoraireEntree))) FROM "+nomSite+" WHERE HoraireSortie is not null AND HoraireEntree is not null";
			ResultSet rs = myConn.getList(sqlCommand);
			short res=0;
			if (rs.next()){
				res=rs.getShort(1);
			}
			return res;
		} catch(Exception e) {
			// Il y avait une erreur
			return -1;
		}
	}
	
	//Durï¿½e min visite en minutes 
	public short getMinVisite(){
		try{
			String sqlCommand = "SELECT min(minute(timediff(HoraireSortie,HoraireEntree))) FROM "+nomSite+" WHERE HoraireSortie is not null AND HoraireEntree is not null";
			ResultSet rs = myConn.getList(sqlCommand);
			short res=0;
			if (rs.next()){
				res=rs.getShort(1);
			}
			return res;
		} catch(Exception e) {
			// Il y avait une erreur
			return -1;
		}
	}
	
	//Affluence quotidienne d'un site
	public short getAffluence(){
		try{
			//Get maximum TranchHoraireVisite
			String sqlCommand ="SELECT COUNT(HOUR(HoraireEntree)) as Compter FROM "+nomSite+" GROUP BY HOUR(HoraireEntree) ORDER BY Compter DESC LIMIT 1";
			ResultSet rs = myConn.getList(sqlCommand);
			short maxAffluence=0;
			if (rs.next()){
				maxAffluence=rs.getShort(1);
			}
			
			//Get affluence quotidienne
			sqlCommand = "SELECT HOUR(HoraireEntree) FROM "+nomSite+" GROUP BY HOUR(HoraireEntree) HAVING COUNT(HOUR(HoraireEntree))="+maxAffluence+"";
			rs = myConn.getList(sqlCommand);
			short res=0;
			if (rs.next()){
				res=rs.getShort(1);
			}
			return res;
		} catch(Exception e) {
			// Il y avait une erreur
			return -1;
		}
	}
	
	//Durï¿½e moyenne visite en minutes 
			public float getMoyenneAvis(){
				try{
					String sqlCommand = "SELECT avg(NiveauSatisfaction) FROM "+nomSite+"";
					ResultSet rs = myConn.getList(sqlCommand);
					float res=0;
					if (rs.next()){
						res=rs.getFloat(1);
					}
					return res;
				} catch(Exception e) {
					// Il y avait une erreur
					return -1;
				}
			}
		
		public static void main(String args[]){
			SiteVisiteDAO t = new SiteVisiteDAO("site2");
			System.out.print(t.estVisite((short)6));
		}
			
}
